﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Microsoft.Data.Sqlite;
using StudentManagementSystem.Models;

namespace StudentManagementSystem
{
    public class DatabaseHelper
    {
        private readonly string connectionString = "Data Source=mydb.db;";

        public void InitializeDatabase()
        {
            using (SqliteConnection conn = new SqliteConnection(connectionString))
            {
                conn.Open();

                string createSchoolTable = "CREATE TABLE IF NOT EXISTS Schools (SchoolId INTEGER PRIMARY KEY, Name TEXT)";
                string createClassTable = "CREATE TABLE IF NOT EXISTS Classes (ClassId INTEGER PRIMARY KEY, ClassName TEXT, SchoolId INTEGER)";
                string createStudentTable = "CREATE TABLE IF NOT EXISTS Students (StudentId INTEGER PRIMARY KEY, Name TEXT, Age INTEGER, ClassId, INTEGER)";
                // 其他创建表的Sql语句...

                using (SqliteCommand cmd = new SqliteCommand(createSchoolTable, conn))
                {
                    cmd.ExecuteNonQuery();
                }

                using (SqliteCommand cmd = new SqliteCommand(createClassTable, conn))
                {
                    cmd.ExecuteNonQuery();
                }

                using (SqliteCommand cmd = new SqliteCommand(createStudentTable, conn))
                {
                    cmd.ExecuteNonQuery();
                }
            }
        }
        public List<School> LoadSchoolsAndClasses()
        {
            // 从数据库中获取所有的School、Class和Student对象
            List<School> schools = GetAllSchools();
            List<Class> classes = GetAllClasses();
            List<Student> students = GetAllStudents();

            // 对每个学校填充其班级列表
            foreach (var school in schools)
            {
                school.Classes = classes.Where(c => c.SchoolId == school.SchoolId).ToList();
            }

            // 对每个班级填充其学生列表
            foreach (var classObj in classes)
            {
                classObj.Students = students.Where(s => s.ClassId == classObj.ClassId).ToList();
            }

            return schools;
        }

        public void InsertSchool(School school)
        {
            using (SqliteConnection conn = new SqliteConnection(connectionString))
            {
                conn.Open();
                string query = "INSERT INTO Schools (SchoolId, Name) VALUES (@SchoolId, @Name)";
                using (SqliteCommand cmd = new SqliteCommand(query, conn))
                {
                    cmd.Parameters.AddWithValue("@SchoolId", school.SchoolId);
                    cmd.Parameters.AddWithValue("@Name", school.Name);
                    cmd.ExecuteNonQuery();
                }
            }
        }
        public void DeleteSchool(int schoolId)
        {
            using (SqliteConnection conn = new SqliteConnection(connectionString))
            {
                conn.Open();
                string query = "DELETE FROM Schools WHERE SchoolId = @SchoolId";
                using (SqliteCommand cmd = new SqliteCommand(query, conn))
                {
                    cmd.Parameters.AddWithValue("@SchoolId", schoolId);
                    cmd.ExecuteNonQuery();
                }
            }
        }
        public void UpdateSchool(School school)
        {
            using (SqliteConnection conn = new SqliteConnection(connectionString))
            {
                conn.Open();
                string query = "UPDATE Schools SET Name = @Name WHERE SchoolId = @SchoolId";
                using (SqliteCommand cmd = new SqliteCommand(query, conn))
                {
                    cmd.Parameters.AddWithValue("@SchoolId", school.SchoolId);
                    cmd.Parameters.AddWithValue("@Name", school.Name);
                    cmd.ExecuteNonQuery();
                }
            }
        }
        public List<School> GetAllSchools()
        {
            List<School> schools = new List<School>();
            using (SqliteConnection conn = new SqliteConnection(connectionString))
            {
                conn.Open();
                string query = "SELECT * FROM Schools";
                using (SqliteCommand cmd = new SqliteCommand(query, conn))
                {
                    using (SqliteDataReader reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            School school = new School
                            {
                                SchoolId = Convert.ToInt32(reader["SchoolId"]),
                                Name = reader["Name"].ToString(),
                            };
                            schools.Add(school);
                        }
                    }
                }
            }
            return schools;
        }
        public void AddClassToSchool(School school, Class newClass)
        {
            // 更新内存中的模型对象
            school.Classes.Add(newClass);

            // 持久化到数据库
            using (SqliteConnection conn = new SqliteConnection(connectionString))
            {
                conn.Open();
                string query = "INSERT INTO Classes (ClassId, ClassName, SchoolId) VALUES (@ClassId, @ClassName, @SchoolId)";
                using (SqliteCommand cmd = new SqliteCommand(query, conn))
                {
                    cmd.Parameters.AddWithValue("@ClassId", newClass.ClassId);
                    cmd.Parameters.AddWithValue("@ClassName", newClass.Name);
                    cmd.Parameters.AddWithValue("@SchoolId", school.SchoolId); // 设置新班级的SchoolId为当前学校的ID
                    cmd.ExecuteNonQuery();
                }
            }
        }

        public void InsertClass(Class classObj)
        {
            using (SqliteConnection conn = new SqliteConnection(connectionString))
            {
                conn.Open();
                string query = "INSERT INTO Classes (ClassId, ClassName, SchoolId) VALUES (@ClassId, @ClassName, @SchoolId)";
                using (SqliteCommand cmd = new SqliteCommand(query, conn))
                {
                    cmd.Parameters.AddWithValue("@ClassId", classObj.ClassId);
                    cmd.Parameters.AddWithValue("@ClassName", classObj.Name);
                    cmd.Parameters.AddWithValue("@SchoolId", classObj.SchoolId);
                    cmd.ExecuteNonQuery();
                }
            }
        }
        public void DeleteClass(int classId)
        {
            using (SqliteConnection connection = new SqliteConnection(connectionString))
            {
                connection.Open();
                string query = "DELETE FROM CLASSES WHERE ClassId = @ClassId";
                using (SqliteCommand cmd = new SqliteCommand(query, connection))
                {
                    cmd.Parameters.AddWithValue("@ClassId", classId);
                    cmd.ExecuteNonQuery ();
                }
            }
        }
        public void UpdateClass(Class classObj)
        {
            using (SqliteConnection con = new SqliteConnection(connectionString))
            {
                con.Open();
                string query = "UPDATE Classes SET Name = @Name WHERE ClassId = @ClassId";
                using (SqliteCommand cmd = new SqliteCommand (query, con))
                {
                    cmd.Parameters.AddWithValue("@ClassId",classObj.ClassId);
                    cmd.Parameters.AddWithValue("@Name", classObj.Name);
                    cmd.ExecuteNonQuery () ;
                } 
            }
        }
        public List<Class> GetClassList()
        {
            List<Class> classes = new List<Class>();
            using (SqliteConnection con = new SqliteConnection(connectionString))
            {
                con.Open();
                string query = "SELECT * FROM Classes";
                using (SqliteCommand cmd = new SqliteCommand(query, con))
                {
                    using (SqliteDataReader reader = cmd.ExecuteReader ())
                    {
                        while (reader.Read ())
                        {
                            Class classObj = new Class
                            {
                                ClassId = Convert.ToInt32(reader["ClassId"]),
                                Name = reader["Name"].ToString(),
                                SchoolId = Convert.ToInt32(reader["SchoolId"])
                            };
                            classes.Add (classObj);
                        }
                    }
                }
            }
            return classes;
        }
        public List<Class> GetAllClasses()
        {
            List<Class> classes = new List<Class>();
            using (SqliteConnection conn = new SqliteConnection(connectionString))
            {
                conn.Open();
                string query = "SELECT * FROM Classes";
                using (SqliteCommand cmd = new SqliteCommand(query, conn))
                {
                    using (SqliteDataReader reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            Class classobj = new Class
                            {
                                ClassId = Convert.ToInt32(reader["ClassId"]),
                                Name = reader["Name"].ToString(),
                                SchoolId = Convert.ToInt32(reader["SchoolId"])
                            };
                            classes.Add(classobj);
                        }
                    }
                }
            }
            return classes;
        }

        public void InsertStudent(Student student)
        {
            using (SqliteConnection conn = new SqliteConnection(connectionString))
            {
                conn.Open();
                string query = "INSERT INTO Classes (ClassId, ClassName, SchoolId) VALUES (@ClassId, @ClassName, @SchoolId)";
                using (SqliteCommand cmd = new SqliteCommand(query, conn))
                {
                    cmd.Parameters.AddWithValue("@StudentId", student.StudentId);
                    cmd.Parameters.AddWithValue("@StudentName", student.Name);
                    cmd.Parameters.AddWithValue("@ClassId", student.ClassId);
                    cmd.ExecuteNonQuery();
                }
            }
        }
        // 删除学生
        public void DeleteStudent(int studentId)
        {
            using (SqliteConnection conn = new SqliteConnection(connectionString))
            {
                conn.Open();
                string query = "DELETE FROM Students WHERE StudentId = @StudentId";
                using (SqliteCommand cmd = new SqliteCommand(query, conn))
                {
                    cmd.Parameters.AddWithValue("@StudentId", studentId);
                    cmd.ExecuteNonQuery();
                }
            }
        }

        // 更新学生信息
        public void UpdateStudent(Student student)
        {
            using (SqliteConnection conn = new SqliteConnection(connectionString))
            {
                conn.Open();
                string query = "UPDATE Students SET StudentName = @StudentName, ClassId = @ClassId WHERE StudentId = @StudentId";
                using (SqliteCommand cmd = new SqliteCommand(query, conn))
                {
                    cmd.Parameters.AddWithValue("@StudentId", student.StudentId);
                    cmd.Parameters.AddWithValue("@StudentName", student.Name);
                    cmd.Parameters.AddWithValue("@ClassId", student.ClassId);
                    cmd.ExecuteNonQuery();
                }
            }
        }

        // 获取所有学生
        public List<Student> GetAllStudents()
        {
            List<Student> students = new List<Student>();
            using (SqliteConnection conn = new SqliteConnection(connectionString))
            {
                conn.Open();
                string query = "SELECT * FROM Students";
                using (SqliteCommand cmd = new SqliteCommand(query, conn))
                {
                    using (SqliteDataReader reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            Student student = new Student
                            {
                                StudentId = Convert.ToInt32(reader["StudentId"]),
                                Name = reader["Name"].ToString(),
                                Age = Convert.ToInt32(reader["Age"]),
                                ClassId = Convert.ToInt32(reader["ClassId"])
                            };
                            students.Add(student);
                        }
                    }
                }
            }
            return students;
        }
    }
}
